/*
 * ProFormaInvoiceDAO.java
 *
 * Created on June 18, 2003, 4:36 PM
 */

package com.scalar.chiptrack.operations.proformainvoice.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.proformainvoice.BankInfo;
import com.scalar.chiptrack.utils.SqlQueryMappings;



/**
 *
 * @author  Balaram Putta
 * @version 
 */

public class ProFormaInvoiceDAO {

    public static final int SHIPPING_PENDING_SALES_ORDERS = 0;
    public static final int INVOICE_DELETED_SALES_ORDERS = 1;
    
    
    public static void deleteInovice( int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try 
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get ("operations.proformainvoice.deleteInovice"));
            pstmt.setInt( 1, salesOrderId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle ) 
        {
            throw new ScalarException( "Could not delete invoice for salesOrderId: " + salesOrderId, sqle );
        }
        finally 
        {
            try 
            {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    
    
    public static ArrayList loadShippingPendingSalesOrderCustomerPOList() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList salesOrderList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.proformainvoice.loadShippingPendingSalesOrderCustomerPOList" ) );
            rset = pstmt.executeQuery();

            while( rset.next() ) 
            {
                salesOrderList.add( new org.apache.struts.util.LabelValueBean (
                                    rset.getString( "SO_Customer_PO_Number" ), 
                                    rset.getString( "SalesOrder_ID" ) ) );
            }
        }
        catch (Exception e) 
        {
            throw new ScalarException ("Could not retrieve sales order IDs and Customer POs from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return salesOrderList;
    }    


    public static ArrayList loadSalesOrderIdAndNumberListByCriteria( int criteria ) throws ScalarException 
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList list = new ArrayList();

        String queryString = null;
        if( criteria == SHIPPING_PENDING_SALES_ORDERS )
        {
            queryString = "operations.proformainvoice.loadShippingPendingSalesOrderIdAndNumberList";
        }
        else if( criteria == INVOICE_DELETED_SALES_ORDERS )
        {
            queryString = "operations.proformainvoice.loadDeletedInvoiceSalesOrderIdAndNumberList";
        }
        else 
        {
            return list;
        }
        
        try 
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( queryString ) );
            rset = pstmt.executeQuery();

            while (rset.next()) {
                list.add( new org.apache.struts.util.LabelValueBean( 
                            rset.getString ( "SalesOrder_No" ), 
                            rset.getString ( "SalesOrder_ID" ) ) );
            }
        }
        catch( Exception e ) 
        {
            throw new ScalarException( "Could not retrieve not-shipped sales order numbers from database", e );
        }
        finally 
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return list;
    }    

    public static ArrayList loadShippingPendingSalesOrderIdAndNumberList() throws ScalarException 
    {
        return loadSalesOrderIdAndNumberListByCriteria( SHIPPING_PENDING_SALES_ORDERS );
    }
    
    
    public static ArrayList loadInvoiceDeletedSalesOrderIdAndNumberList() throws ScalarException 
    {
        return loadSalesOrderIdAndNumberListByCriteria( INVOICE_DELETED_SALES_ORDERS );
    }

  /** Added by  Sarada on 14th Nov 2003
    * To update/insert Banking Info which we enter through Proforma Invoice
	*/
	public static boolean updateBankInfo ( String bankName , String address , String city , String state , String zipcode , String country , String abavalue , String acct , String swiftcode , int salesOrderId )throws ScalarException
	{
		Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
		boolean success = false;
       
        try 
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.proformainvoice.loadBankInformation" ) );
            pstmt.setInt ( 1 , salesOrderId );

            rs = pstmt.executeQuery();

            //Modified by Sarada on 10th Feb 2004 to insert/update bank info
            if(rs.next() == false)
            {
                  pstmt.close();
                  pstmt = null;
                  pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.proformainvoice.insertBankInformation" ) );
            }
            else
            {
                  pstmt.close();
                  pstmt = null;
                  pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.proformainvoice.updateBankInformation" ) );
            }
			pstmt.setString (1, bankName);
			pstmt.setString (2, address);
			pstmt.setString (3, city);
			pstmt.setString (4, state);
			pstmt.setString (5, zipcode);
			pstmt.setString (6, country);
			pstmt.setString (7, abavalue);
			pstmt.setString (8, acct);
			pstmt.setString (9, swiftcode);
            pstmt.setInt (10, salesOrderId);


            pstmt.executeUpdate();
			success = true;
          
        }
        catch( Exception e ) 
        {
            throw new ScalarException( "Could not insert Bank information into database", e );
        }
        finally 
        {
           
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

		try{
			if ( conn != null )
			 {
				if ( success == true )
					conn.commit();
				else
					conn.rollback();
			 }
		  } catch (SQLException ignore) {}

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
		return success;
        
	}//method
  /** Added by Sarada on 14th Nov 2003 
    * To load Bank Information from Database,this method takes  salesorderId as parameter and returns BankInfo Object
	*/
	public static BankInfo loadBankInfo(int salesOrderId)throws ScalarException
	{
        Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
        ResultSet rs1 = null;
		BankInfo bankInfo = new BankInfo();

		try
		{
          conn = DataSource.getConnection();
		  pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.proformainvoice.loadBankInformation" ) );
          pstmt.setInt( 1 , salesOrderId );
          rs = pstmt.executeQuery();

            //Modified by Sarada on 18th Feb 2004 to to get BankInfo
		  if(rs.next() == true )
		  {
             // System.out.println("IF");
			  bankInfo.setBankName(rs.getString("BankName"));
			  bankInfo.setAddress(rs.getString("Address"));
			  bankInfo.setCity(rs.getString("City"));
			  bankInfo.setState(rs.getString("State"));
			  bankInfo.setZipCode(rs.getString("ZipCode"));
			  bankInfo.setCountry(rs.getString("Country"));
			  bankInfo.setAba(rs.getString("ABA"));
			  bankInfo.setAcct(rs.getString("Acct"));
			  bankInfo.setSwiftCode(rs.getString("SwiftCode"));
		  }
          else
          {
          	  // Modified by Sarada on 27th Feb 2004 to get correct bank info 
			  pstmt = conn.prepareStatement (SqlQueryMappings.get ("users.getBankId"));
			  rs1 = pstmt.executeQuery();
			  int bankid = 0 ;
			  if(rs1.next())
			  {
				bankid = rs1.getInt("BankId");
			  }
			  pstmt.close();
			  pstmt = null; 
			  rs1.close();
			  
			  //System.out.println("ELSE  "+bankid);
              pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.proformainvoice.loadBankDetails" ) );
              
			  pstmt.setInt( 1 , bankid );
			  rs1 =  pstmt.executeQuery();
			  
           
              while( rs1.next())
              {
                  bankInfo.setBankName(rs1.getString("BankName"));
                  bankInfo.setAddress(rs1.getString("Address"));
                  bankInfo.setCity(rs1.getString("City"));
                  bankInfo.setState(rs1.getString("State"));
                  bankInfo.setZipCode(rs1.getString("ZipCode"));
                  bankInfo.setCountry(rs1.getString("Country"));
                  bankInfo.setAba(rs1.getString("ABA"));
                  bankInfo.setAcct(rs1.getString("Acct"));
                  bankInfo.setSwiftCode(rs1.getString("SwiftCode"));
              }

          }

        }catch(Exception e){throw new ScalarException( "Could not get Bank information from database", e );}

       finally
		{
			if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }

		}   

       return bankInfo;

    }//method



}
